//
//  CPYDataBaseTool.m
//  Summer
//
//  Created by chenpengyu on 16/3/17.
//  Copyright © 2016年 cpy. All rights reserved.
//

#import "CPYDataBaseTool.h"
#import <sqlite3.h>

@interface CPYDataBaseTool ()
// 数据库存储路径
@property (nonatomic, copy) NSString *dataBasePath;

@end
// 定义全局变量，保证只有一个CPYDataBaseTool对象
static CPYDataBaseTool *dataBaseTool = nil;

@implementation CPYDataBaseTool

// 单例设计模式
// 保证整个工程在任何时候获取的dataBaseTool对象相同
+ (instancetype)dataBaseManager {
    if (!dataBaseTool) {
        dataBaseTool = [[CPYDataBaseTool alloc] init];
    }
    return dataBaseTool;
}
#pragma mark - 获取数据库存储路径

- (NSString *)dataBasePath {
    if (!_dataBasePath) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES );
        NSString *path = [paths lastObject];
        self.dataBasePath = [path stringByAppendingString:@"/songList.sqlite"];
    }
    return _dataBasePath;
}

#pragma mark - 打开数据库

// 定义一个数据库指针
static sqlite3 *db = nil;

- (void)openDataBase {
    //    打印路径 查看数据库时候创建成功
    NSLog(@"%@", self.dataBasePath);
    //    如果数据库存在的话，直接开启。如果不存在的话，先创建再开启
    int result = sqlite3_open(self.dataBasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库代开失败");
    }
}
#pragma mark - 关闭数据库
- (void)closeDataBase {
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    }else {
        NSLog(@"数据库关闭失败");
    }
}
#pragma mark - 创建数据库
- (void)createTable {
    //    准备SQL语句
    NSString *sqlString = @"create table if not exists songList(num integer primary key autoincrement, Id text not null, song text not null, singer text not null, image text not null, url text not null)";
    //    执行SQL语句
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    }else {
        NSLog(@"创建失败");
    }
}
#pragma mark - 插入数据
- (void)insertDataBase:(CPYDiscoveryPageSongListModel *)songDetailModel {
    CPYDiscoveryPageUrlListModel *urlModel = songDetailModel.urlList[0];

//    准备SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"insert into songList (Id, song, singer, image, url) values ('%@','%@', '%@','%@', '%@')", songDetailModel.songId, songDetailModel.name, songDetailModel.singerName, songDetailModel.picUrl, urlModel.url];
    //    获取伴随指针 － 数据库的动态指针
    sqlite3_stmt *stmt = NULL;
    //    预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
//        绑定参数
//        sqlite3_bind_text(stmt, 0, Id.UTF8String, -1, NULL);
//        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
//        sqlite3_bind_text(stmt, 2, url.UTF8String, -1, NULL);
    //    执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入成功");
        }else {
            NSLog(@"插入失败");
        }
    }else {
        NSLog(@"语句错误");
    }
//    关闭伴随指针
    sqlite3_finalize(stmt);
}

#pragma mark - 删除数据
- (void)deleteDataBase:(NSString *)Id {
    //    准备SQL语句
    NSString *sqlString = @"delete from songList where Id = ?";
    //    获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //    绑定参数
        sqlite3_bind_text(stmt, 1, Id.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除成功");
        }else {
            NSLog(@"删除失败");
        }
    }else {
        NSLog(@"语句错误");
    }
    //    关闭伴随指针
    sqlite3_finalize(stmt);
}
#pragma mark - 修改数据
- (void)updataDataBase:(NSString *)url withId:(NSString *)Id {
    //    准备SQL语句
    NSString *sqlString = @"update songList set url = ? where Id = ?";
    //    获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
    //    绑定参数
        sqlite3_bind_text(stmt, 2, url.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 0, Id.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"修改成功");
        }else {
            NSLog(@"修改失败");
        }
    }else {
        NSLog(@"语句错误");
    }
    //    关闭伴随指针
    sqlite3_finalize(stmt);
}
#pragma mark - 查找数据
- (CPYSONGSModel *)selectDataBase {
    CPYSONGSModel *songsModel = [[CPYSONGSModel alloc] init];
    songsModel.songs = [NSMutableArray arrayWithCapacity:0];

    //    准备SQL语句
    NSString *sqlString = @"select Id, song, singer, image, url from songList";
    //    获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        绑定参数
//        sqlite3_bind_int64(stmt, 0, Id);
        //        执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
//            //            从伴随指针获取数据
            CPYDiscoveryPageSongListModel *songDetailModel = [[CPYDiscoveryPageSongListModel alloc] init];
            songDetailModel.urlList = [NSMutableArray array];
            CPYDiscoveryPageUrlListModel *urlModel = [[CPYDiscoveryPageUrlListModel alloc] init];
            NSString *selectId = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 0)];
            NSString *selectSong = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
            NSString *selectSinger = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 2)];
            NSString *selectImage = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)];
            NSString *selectURL = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 4)];

            urlModel.url = selectURL;
            songDetailModel.name = selectSong;
            songDetailModel.singerName = selectSinger;
            songDetailModel.picUrl = selectImage;
            songDetailModel.songId = @(selectId.integerValue);
            [songDetailModel.urlList addObject:urlModel];
            [songsModel.songs addObject:songDetailModel];
        }
    }else {
        NSLog(@"语句错误");
    }
    //    关闭伴随指针
    sqlite3_finalize(stmt);
    return songsModel;
}
#pragma mark - 判断是否存有此页数据
- (BOOL)selectDataBaseWithId:(NSString *)Id {
    NSString *selectId = [NSString string];
    BOOL isHave = nil;
    //    准备SQL语句
    NSString *sqlString = @"select Id from songList";
    //    获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
//                绑定参数
        sqlite3_bind_text(stmt, 1, Id.UTF8String, -1, NULL);
//                执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据
            selectId = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 0)];
            if (selectId.integerValue == Id.integerValue) {
                isHave = YES;
            }else {
//                isHave = NO;
            }
        }
    }else {
        NSLog(@"语句错误");
        return NO;
    }
    //    关闭伴随指针
    sqlite3_finalize(stmt);
    if (isHave == YES) {
        NSLog(@"已有此页数据");
        return YES;
    }else {
        NSLog(@"未有此页数据");
        return NO;
    }
}



@end
